A multi-tenant application system provides a multi-tenant application to users of software through multi-tenant (MT) technologies, i.e., running a single instance of a software application on a server of a service provider to provide software application services for a plurality of tenants (e.g., organizations like enterprises), thus reducing the costs of the development, deployment and operation of the software application.
A database is a systematic data organization for performing effective and reliable storage, retrieval and processing of the data. A database is composed of tables, in which there are rows of associated data. The access to and operation on the data are accomplished by a Structured Query Language (SQL) query.
A basic-database is a typical type of database, which includes business data, for example, a database store of daily sales data of a store, including commodities, sales volumes, sale prices and sale times.
Data analysis can be made according to the basic-database used by the SQL query. For example, calculating the total daily sales volume of each commodity from the perspective of the commodity; as time goes by, calculating the total monthly sales volume of each commodity, and calculating the total annual sales volume of each commodity.
In the context of the present invention, a basic-database is also called a basic-table BT.
A materialized view (MV), also called a materialized table, can be created from a basic-table. The materialized table is also a kind of table in a database, which can store intermediate data that is exported from the basic-table, e.g., the aforesaid total monthly sales volume of a commodity. The materialized table can be used to query, and by using the intermediate data in the materialized table, the amount of calculation required by the complex query can be reduced, and thus the query efficiency is improved.
In a massive multi-tenant application system, materialized tables are widely used to improve the efficiency of data query operations. In such a case, a plurality of tenants share one or more basic-tables as well as materialized tables corresponding to the basic-tables, and usually tenant identifiers are used in the basic-tables and the materialized tables to distinguish between the tenants to which the data belongs.
In the design of a materialized table in a database, the basic-table and the materialized table maintain data synchronization there between. When operations, such as addition, deletion and modification, are made to the basic-table, the materialized table can only be queried and retrieved from after refreshing and synchronization. During the process of refreshing and synchronization of the materialized table, the materialized table will be locked. At this time, a SQL query of the user cannot act on the materialized table. For example, when the sales volume of the commodity of a new day is added into the basic-table, the corresponding materialized table cannot be used, since the monthly sales volume of the commodity in the materialized table does not yet reflect the sales volume of the commodity of the newly-added day, and if the data in the materialized table is used, the result will be incorrect. After performing operations, such as addition, deletion, and modification, to the basic-table, the materialized table can only be used after its data is synchronized with the data of the modified basic-table. For example, according to the modified basic-table, a materialized table is exported or generated again so that the total monthly sales volume of the commodity of the materialized table includes the total sales volume of the newly-added day.
In a multi-tenant application system using materialized tables, different tenants may have different update patterns for a shared basic-table, e.g., performing update operations of data addition, deletion or modification on the basic-table at different times and/or in different frequencies. If there are many tenants sharing the basic-table and the materialized table, then at any time, there may be tenants performing update operations on the common one or more shared basic-tables. Since a materialized table corresponds to a plurality of basic-tables, from the perspective of the materialized table, the update rate of the basic-tables is always very high. As a result, the refreshing probability of the materialized table is high, and the frequency of it being locked is also high. This makes the tenants almost unable to use the materialized table to improve the query efficiency, thus affecting the speed of business operations by the tenants.